package com.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.db.DBConnect;
import com.model.Calcu;
import com.model.GoodStudentModel;
public class CalcuControl {
	
	
	//统计学生的所有课程成绩
	public ArrayList<Calcu> CalcuStudent() {
		ArrayList<Calcu> list = new ArrayList<Calcu>();
		DBConnect dbc = new DBConnect();
		String sql = "select student_id, " +
				"max(point) as max, min(point) as min, avg(point) as avg from score group by student_id order by avg desc";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next()) {
				Calcu calcu = new Calcu();
				calcu.setStudent_id(rs.getInt("student_id"));
				calcu.setAve(rs.getFloat("avg"));
				calcu.setMax(rs.getFloat("max"));
				calcu.setMin(rs.getFloat("min"));
				list.add(calcu);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}

	//统计某门课程的所有学生成绩
	public ArrayList<Calcu> CalcuCourse() {
		ArrayList<Calcu> list = new ArrayList<Calcu>();
		DBConnect dbc = new DBConnect();
		String sql = "select course_id, (select count(*) as fail_num from score where point <60) as fail_num," +
				"max(point) as max, min(point) as min, avg(point) as avg from score group by course_id order by avg desc";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next()) {
				Calcu calcu = new Calcu();
				calcu.setCourse_id(rs.getInt("course_id"));
				calcu.setAve(rs.getFloat("avg"));
				calcu.setMax(rs.getFloat("max"));
				calcu.setMin(rs.getFloat("min"));
				calcu.setFail_num(rs.getInt("fail_num"));
				list.add(calcu);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
	

	//统计优秀学生
	public ArrayList<Calcu> CalcuTop() {
		ArrayList<Calcu> list = new ArrayList<Calcu>();
		DBConnect dbc = new DBConnect();
		String sql = "select student_id, avg(point) as avg from score group by student_id having avg(point) > 85";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next()) {
				Calcu calcu = new Calcu();
				calcu.setAve(rs.getFloat("avg"));
				calcu.setStudent_id(rs.getInt("student_id"));
				list.add(calcu);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}

	public ArrayList<GoodStudentModel> getGoodStudent() {
		ArrayList<GoodStudentModel> list = new ArrayList<GoodStudentModel>();
		DBConnect dbc = new DBConnect();
		String sql = "select student_id, avg(point) as avg from score group by student_id having avg(point) > 85";
		dbc.prepareStatement(sql);
		try {
			ResultSet rs = dbc.executeQuery();
			while(rs.next()) {
				GoodStudentModel model = new GoodStudentModel();
				model.setStudent_id(rs.getInt("student_id"));
				model.setAvg(rs.getFloat("avg"));
				list.add(model);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			dbc.close();
		}
	}
}

